Relational database management systems provide data row storage options that allow data row storage to be varied from a selected native key sequence. Various data storage options may be utilized, for example, “clustered” (which attempts to place the data rows in native key sequence but allows some deviation if space becomes a concern), “sequential” (which places new data rows at the end of the existing space and when space is exhausted, wraps back to the front of a table and begins adding data rows there), “random” (which places data rows wherever there is available space). The “random” option is most commonly used because it consumes the least amount of resources.
For most indexed access of data rows, the actual data row order in the table has no effect on the performance of the database or the accessing application. However, various applications require access to large segments of data rows using a sequential (read next) process. In these cases, the native key sequence may be selected to access the data rows.
Over a period of time with additions and deletions of data rows from the database table, the data row sequence becomes disorganized. When this disorganization occurs, the application accessing the data rows according to native key sequence slows because the database management system is required to perform substantially more physical IOs to retrieve the data rows in the native key sequence.
In order to re-order the data rows according to the native key sequence, database reorganization processes may be regularly scheduled. However, database reorganizations may be resource-intensive and in some cases unnecessary. In these cases, database resources may be wasted when reorganizing database tables that do not need reorganization. Thus, what is needed is a low cost (resource-wise) way to determine whether at least a portion of a sequential-access database will benefit from reorganization.
These and other drawbacks exist.